#!/usr/bin/env python3
# -*- coding:utf-8 -*-
"""

仅该文件可以对距离数据库进行写入
利用高德地图api实现计算两点间的距离
在运行search_jw之后，需要同时运行此部分

"""

import requests
import pymysql


def calculate(start_lon, start_lat, dest_lon, dest_lat):
    request_json = requests.get('https://restapi.amap.com/v3/distance?'
                                f'origins={start_lon},{start_lat}'
                                f'&destination={dest_lon},{dest_lat}'
                                f'&type=1'
                                '&output=JSON'
                                '&key=6487568d7a414da8b2dd04105d09748b')

    distance = request_json.json()['results'][0]['distance']

    return distance


if __name__ == '__main__':
    database = pymysql.connect(host="localhost", user="root", password="zzh200086", db="dachuang")
    cursor = database.cursor()

    sql = "select id,longitude,latitude from destination"
    cursor.execute(sql)
    sql_infos = cursor.fetchall()

    sites_id = []
    sites_x = []
    sites_y = []

    for sql_info in sql_infos:
        sites_id.append(sql_info[0])
        sites_x.append(sql_info[1])
        sites_y.append(sql_info[2])

    for start in range(0, len(sites_id)):  # i是起点
        for end in range(0, len(sites_id)):  # j是终点
            if start != end:
                distance = calculate(start_lon=sites_x[start], start_lat=sites_y[start], dest_lon=sites_x[end],
                                     dest_lat=sites_y[end])
            else:
                distance = 0

            print("start:", sites_id[start], "end:", sites_id[end], "distance:", distance)
            sql = f"insert ignore into distances (start, end, distance) " \
                  f"VALUES ({sites_id[start]}, {sites_id[end]}, {distance}) "

            cursor.execute(sql)
            database.commit()

    sql = "select id,longitude,latitude from center"
    cursor.execute(sql)
    sql_infos = cursor.fetchall()
    center_id = []
    center_x = []
    center_y = []

    for sql_info in sql_infos:
        center_id.append(sql_info[0])
        center_x.append(sql_info[1])
        center_y.append(sql_info[2])

    for start in range(0, len(center_id)):  # i是起点
        for end in range(0, len(sites_id)):  # j是终点

            distance = calculate(start_lon=center_x[start], start_lat=center_y[start], dest_lon=sites_x[end],
                                 dest_lat=sites_y[end])

            print("start:", center_id[start], "end:", sites_id[end], "distance:", distance)
            sql = f"insert ignore into distances (start, end, distance) " \
                  f"VALUES ({center_id[start]}, {sites_id[end]}, {distance}) "

            cursor.execute(sql)
            database.commit()

    for start in range(0, len(sites_id)):  # i是起点
        for end in range(0, len(center_id)):  # j是终点

            distance = calculate(start_lon=sites_x[start], start_lat=sites_y[start], dest_lon=center_x[end],
                                 dest_lat=center_y[end])

            print("start:", sites_id[start], "end:", center_id[end], "distance:", distance)
            sql = f"insert ignore into distances (start, end, distance) " \
                  f"VALUES ({sites_id[start]}, {center_id[end]}, {distance}) "

            cursor.execute(sql)
            database.commit()

    pass
